package com.java.business.report.service;

import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.java.business.report.bean.T_REPORT_TPL;
import com.java.business.report.dao.ReportMapper;
import com.java.business.system.common.dao.CommonMapper;
import com.java.core.bean.LowerCaseKeyMap;
import com.java.core.bean.ResultBean;
import com.java.core.excel.ExcelModel;
import com.java.core.excel.ExcelTemplatelHandler;
import com.java.core.utils.DateUtils;
import com.java.core.utils.FileUtils;
import com.java.core.utils.RequestUtils;

@Transactional
@Service
public class ReportService {
	@Autowired
	private ReportMapper mapper;

	@Autowired
	private CommonMapper commonMapper;

	@Autowired
	private JdbcTemplate jdbc;

	public LowerCaseKeyMap getReport(String id) {
		LowerCaseKeyMap report = mapper.getReport(id);
		report.put("columns", report.get("cols"));
		String group = (String) report.get("headergroup");
		if (StringUtils.isNotBlank(group)) {
			JSONArray array = JSONArray.parseArray(group);
			report.put("headergroup", array);
		}
		report.put("cols", mapper.getColList(id));

		// 组装查询条件
		List<Object> fields = new ArrayList<Object>();
		List<LowerCaseKeyMap> querys = mapper.getQueryList(id);
		for (LowerCaseKeyMap map : querys) {
			String columnname = (String) map.get("columnname");
			String control = (String) map.get("control");
			String chinesename = (String) map.get("chinesename");
			JSONObject field = JSONObject.parseObject(control);
			field.put("name", columnname);
			field.put("fieldLabel", chinesename);
			String value = field.getString("value");
			if (StringUtils.isNotBlank(value)) {
				if ("@now()".equals(value)) {
					value = DateUtils.now("yyyy-MM-dd");
					field.put("value", value);
				}
			}
			fields.add(field);
		}
		report.put("querys", fields);
		return report;
	}

	/**
	 * 通用查询方法
	 * 
	 * @param id
	 * @return
	 */
	public List<LowerCaseKeyMap> query(Map<String, String> params) {
		String id = params.get("id");
		String sql = mapper.getSql(id);
		if (StringUtils.isBlank(params.get("where"))) {
			sql = sql.replaceAll("\\{where\\}", " ");
		} else {
			sql = sql.replaceAll("\\{where\\}", params.get("where"));
		}
		// 获取配置信息
		LowerCaseKeyMap report = mapper.getReport(id);
		String hastotal = String.valueOf(report.get("hastotal"));
		String showcol = String.valueOf(report.get("showcol"));
		String subcols = "", datacols = "";
		List<LowerCaseKeyMap> cols = mapper.getColList(id);
		for (LowerCaseKeyMap col : cols) {
			if ("1".equals(String.valueOf(col.get("isdata")))) {
				if (datacols.length() > 0) {
					datacols += ",";
				}
				datacols += String.valueOf(col.get("columnname"));
			}
			if ("1".equals(String.valueOf(col.get("isgroup")))) {
				if (subcols.length() > 0) {
					subcols += ",";
				}
				subcols += String.valueOf(col.get("columnname"));
			}
		}
		params.put("sql", sql);
		List<LowerCaseKeyMap> list = mapper.query(params);
		// 小计
		if (subcols.length() > 0) {
			list = mapper.query(params);
			subtotal(list, showcol, subcols.toLowerCase().split(","), datacols
					.toLowerCase().split(","));
		}

		// 合计
		if ("1".equals(hastotal)) {
			sum(list, showcol, datacols.toLowerCase().split(","));
		}
		return list;
	}

	/**
	 * 分页查询
	 * 
	 * @param params
	 * @return
	 */
	public ResultBean queryForPaigng(Map<String, String> params) {
		ResultBean rb = new ResultBean();
		String id = params.get("id");
		String sql = mapper.getSql(id);
		if (StringUtils.isBlank(params.get("where"))) {
			sql = sql.replaceAll("\\{where\\}", " ");
		} else {
			sql = sql.replaceAll("\\{where\\}", params.get("where"));
		}
		// 判断是否分页
		String haspaging = params.get("haspaging");
		// 如果有小计时，强制不分页
		if ("1".equals(haspaging)) {
			// 获取配置信息
			LowerCaseKeyMap report = mapper.getReport(id);
			String hastotal = String.valueOf(report.get("hastotal"));
			String showcol = String.valueOf(report.get("showcol"));
			String subcols = "", datacols = "";
			List<LowerCaseKeyMap> cols = mapper.getColList(id);
			for (LowerCaseKeyMap col : cols) {
				if ("1".equals(String.valueOf(col.get("isdata")))) {
					if (datacols.length() > 0) {
						datacols += ",";
					}
					String columnname = String.valueOf(col.get("columnname"));
					datacols += "sum(" + columnname + ") " + columnname;
				}
				if ("1".equals(String.valueOf(col.get("isgroup")))) {
					if (subcols.length() > 0) {
						subcols += ",";
					}
					subcols += String.valueOf(col.get("columnname"));
				}
			}
			// 分页SQL
			String pagingSql = "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ({SQL}) A WHERE ROWNUM <= #{start}+#{limit} ) WHERE RN >= #{start}+1";
			// 总数SQL
			String countSql = "SELECT COUNT(1) FROM ({SQL})";
			// 合计SQL
			String totalSql = "select " + datacols + " from ({SQL})";
			pagingSql = pagingSql.replace("{SQL}", sql);
			countSql = countSql.replace("{SQL}", sql);
			totalSql = totalSql.replace("{SQL}", sql);

			params.put("sql", pagingSql);
			List<LowerCaseKeyMap> list = mapper.query(params);
			// 合计
			if ("1".equals(hastotal)) {
				Map<String, String> tempParam = new HashMap<String, String>();
				tempParam.put("sql", totalSql);
				LowerCaseKeyMap totalRec = commonMapper.queryForMap(tempParam);
				totalRec.put(showcol, "合计");
				list.add(0, totalRec);
			}
			rb.setTotal(jdbc.queryForObject(countSql, Integer.class));
			rb.setData(list);
		} else {
			List<LowerCaseKeyMap> list = query(params);
			rb.setData(list);
			rb.setTotal(list.size());
		}
		return rb;
	}

	@SuppressWarnings("unchecked")
	public void export2Excel(HttpServletResponse response,
			HttpServletRequest request) throws IOException,
			InvalidFormatException {
		Map<String, String> params = RequestUtils.getParams(request);
		LowerCaseKeyMap report = mapper.getReport(params.get("id"));
		String exceltpl = (String) report.get("exceltpl");
		Integer excelstart = Integer.valueOf(String.valueOf(report
				.get("excelstart")));
		String name = (String) report.get("name");
		String filename = name + ".xls";
		ExcelModel em = ExcelModel.XLS;

		List<LowerCaseKeyMap> list = query(params);
		if (list.size() > 10000) {
			em = ExcelModel.XLSX_BIGDATA;
			filename = name + ".xlsx";
		}
		// 设置header
		response.reset();
		response.setHeader("content-disposition", "attachment;filename=\""
				+ URLEncoder.encode(filename, "UTF-8") + "\"");
		response.setHeader("connection", "close");
		// 输出流
		OutputStream out = new BufferedOutputStream(response.getOutputStream());
		// 模板
		InputStream in;
		T_REPORT_TPL tpl = mapper.getTpl(exceltpl);
		byte[] binary = tpl.getTpl();
		if (binary != null) {
			in = new ByteArrayInputStream(binary);
		} else {
			File file = new File(request.getServletContext().getRealPath(
					"/exceltpl/" + exceltpl));
			in = new FileInputStream(file);
		}
		// 执行导出
		ExcelTemplatelHandler handler = new ExcelTemplatelHandler(em);
		handler.export(out, in, excelstart, new LowerCaseKeyMap(), list);
	}

	/**
	 * 计算小计
	 * 
	 * @param list
	 */
	private void subtotal(List<LowerCaseKeyMap> list, String showcol,
			String[] subcols, String[] datacols) {
		showcol = showcol.toLowerCase();
		List<Map<String, String>> groups = getGroups(list, subcols);
		// 分组数据
		for (int j = 0; j < groups.size(); j++) {
			Map<String, String> group = groups.get(j);
			Set<String> groupValues = group.keySet();
			for (String groupValue : groupValues) {
				String subCol = group.get(groupValue);
				LowerCaseKeyMap subtotalData = new LowerCaseKeyMap();
				Map<String, Double> subtotalGroups = new HashMap<String, Double>();
				// 循环计算小计
				for (LowerCaseKeyMap data : list) {
					Object value = data.get(subCol);
					if (groupValue.equals(String.valueOf(value))) {
						for (String dataCol : datacols) {
							Double subtotal = subtotalGroups.get(dataCol);
							if (subtotal == null) {
								subtotal = 0d;
							}
							subtotal += getDouble(data.get(dataCol));
							subtotalGroups.put(dataCol, subtotal);
						}
					}
				}
				String escape = StringUtils.repeat("&emsp;", j);
				// 设置小计行的公共列
				subtotalData.put("flag", "1");
				subtotalData.put(subCol, "");
				subtotalData.put(showcol, escape + groupValue);
				for (String key : subtotalGroups.keySet()) {
					subtotalData.put(key, subtotalGroups.get(key));
				}
				// 将小计行加进列表
				for (int i = 0; i < list.size(); i++) {
					LowerCaseKeyMap data = list.get(i);
					if (groupValue.equals(String.valueOf(data.get(subCol)))) {
						list.add(i, subtotalData);
						break;
					}
				}
			}
			for (LowerCaseKeyMap data : list) {
				// 如果存在flag字段，且flag等于1时，不合计
				if (String.valueOf(data.get("flag")).equals("1")) {
					continue;
				}
				Object v = data.get(showcol);
				data.put(showcol, "&emsp;" + (v == null ? "" : v));
			}
		}
	}

	/**
	 * 获取分组数据
	 * 
	 * @param list
	 * @param subcols
	 * @return
	 */
	private List<Map<String, String>> getGroups(List<LowerCaseKeyMap> list,
			String[] subcols) {
		List<Map<String, String>> groups = new ArrayList<Map<String, String>>();
		for (String subcol : subcols) {
			Map<String, String> group = new HashMap<String, String>();
			for (LowerCaseKeyMap data : list) {
				String value = String.valueOf(data.get(subcol));
				group.put(value, subcol);
			}
			groups.add(group);
		}
		return groups;
	}

	/**
	 * 计算合计列
	 * 
	 * @param list
	 */
	private void sum(List<LowerCaseKeyMap> list, String showcol,
			String[] datacols) {
		LowerCaseKeyMap rec = new LowerCaseKeyMap();
		if (list.size() == 0) {
			return;
		}
		for (String key : datacols) {
			double sum = 0;
			for (LowerCaseKeyMap data : list) {
				// 如果flag等于1时，不合计
				if (String.valueOf(data.get("flag")).equals("1")) {
					continue;
				}
				sum += getDouble(data.get(key));
			}
			rec.put(key, sum);
		}
		if (StringUtils.isNotBlank(showcol)) {
			rec.put(showcol, "合计");
		}
		list.add(0, rec);
	}

	// 获取转换后的浮点数据
	private double getDouble(Object value) {
		if (value == null) {
			return 0;
		}
		return Double.valueOf(String.valueOf(value));
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<LowerCaseKeyMap> getQueryColName(String sql) {
		sql = sql.replaceAll("\\{where\\}", " ");
		final List<LowerCaseKeyMap> list = new ArrayList<LowerCaseKeyMap>();
		jdbc.query(sql, new ResultSetExtractor() {
			@Override
			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				int count = rs.getMetaData().getColumnCount();
				for (int i = 1; i <= count; i++) {
					LowerCaseKeyMap map = new LowerCaseKeyMap();
					String columnName = rs.getMetaData().getColumnName(i);
					map.put("columnname", columnName);
					map.put("chinesename", columnName);
					map.put("width", 120);
					map.put("display", 1);
					map.put("priority", i);
					map.put("renderer", "");
					list.add(map);
				}
				return null;
			}
		});
		return list;
	}

	public void addTpl(T_REPORT_TPL tpl) {
		mapper.addTpl(tpl);
	}

	public void updateTpl(T_REPORT_TPL tpl) {
		if (tpl.getTpl() == null) {
			mapper.updateTplName(tpl);
		} else {
			mapper.updateTpl(tpl);
		}
	}

	public void deleteTpl(T_REPORT_TPL tpl) {
		mapper.deleteTpl(tpl.getTplid());
	}

	public void downloadTpl(HttpServletResponse response, T_REPORT_TPL tpl)
			throws IOException {
		tpl = mapper.getTpl(tpl.getTplid());
		if (tpl.getTpl() != null) {
			FileUtils.download(response, tpl.getTplname(), tpl.getTpl());
			tpl.getTpl();
		}
	}
}
